﻿using System;
using System.IO; 
using System.Collections.Generic;
using System.Linq;
using System.Text;

using DocumentFormat.OpenXml.Packaging;
using Ap = DocumentFormat.OpenXml.ExtendedProperties;
using Vt = DocumentFormat.OpenXml.VariantTypes;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using X14 = DocumentFormat.OpenXml.Office2010.Excel;
using A = DocumentFormat.OpenXml.Drawing;



namespace HDI.Charts2
{

   public  static class Chart2Excel
    {


        public static MemoryStream ToExcel(this Chart chart)
        {
            return CreatePackage(chart);
        }

        // Creates a SpreadsheetDocument.
        public static MemoryStream CreatePackage(Chart chart)
        {
            MemoryStream docStream =new MemoryStream();
            using (SpreadsheetDocument package = SpreadsheetDocument.Create(docStream, SpreadsheetDocumentType.Workbook))
            {
                CreateParts(package, chart);

            }


            return docStream;
        }

        // Adds child parts and generates content of the specified part.
        private static void CreateParts(SpreadsheetDocument document, Chart chart)
        {
            ExtendedFilePropertiesPart extendedFilePropertiesPart = document.AddNewPart<ExtendedFilePropertiesPart>("rId3");
            GenerateExtendedFileProperties(extendedFilePropertiesPart);

            WorkbookPart workbookPart = document.AddWorkbookPart();
            GenerateWorkbookPartContent(workbookPart,chart.Title);

            WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>("rId1");
            GenerateWorksheetPartContent(worksheetPart, chart);

            SetPackageProperties(document);
        }
        

        // Generates content of extendedFilePropertiesPart1.
        private  static  void GenerateExtendedFileProperties(ExtendedFilePropertiesPart FileProperties)
        {
            Ap.Properties properties1 = new Ap.Properties();
            properties1.AddNamespaceDeclaration("vt", "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes");
            Ap.TotalTime totalTime1 = new Ap.TotalTime();
            totalTime1.Text = "0";
            Ap.Application application1 = new Ap.Application();
            application1.Text = "Microsoft Excel";
            Ap.DocumentSecurity documentSecurity1 = new Ap.DocumentSecurity();
            documentSecurity1.Text = "0";
            Ap.ScaleCrop scaleCrop1 = new Ap.ScaleCrop();
            scaleCrop1.Text = "false";

         

            Ap.ApplicationVersion applicationVersion1 = new Ap.ApplicationVersion();
            applicationVersion1.Text = "14.0300";

            properties1.Append(totalTime1);
            properties1.Append(application1);
            properties1.Append(documentSecurity1);
            properties1.Append(scaleCrop1);
          
            

            properties1.Append(applicationVersion1);

            FileProperties.Properties = properties1;
        }

        // Generates content of workbookPart1.
        private  static  void GenerateWorkbookPartContent(WorkbookPart workbookPart, String title)
        {
            Workbook workbook = new Workbook();
            workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "5", LowestEdited = "5", BuildVersion = "9302" };
          

            Sheets sheets = new Sheets();
            Sheet sheet1 = new Sheet() { Name =title, SheetId = (UInt32Value)1U, Id = "rId1" };
           

            sheets.Append(sheet1);

            CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)144525U };

            workbook.Append(fileVersion1);

            workbook.Append(sheets);
            workbook.Append(calculationProperties1);

            workbookPart.Workbook = workbook;
        }


        // Generates content of worksheetPart3.
        private  static  void GenerateWorksheetPartContent(WorksheetPart worksheetPart, Chart chart)
        {
            Worksheet worksheet = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
          

            

            SheetData sheetData = new SheetData();
            Row firstRow = new Row();

            foreach (Series serie in chart.Daten)
            {
                if (serie.Count == 0 && serie.Option.ContainsKey("Formula"))
                {
                    Coord.createFormulaData(serie);
                }
            }

            var x = from series in chart.Daten
                    from data in series
                    group data.Name by data.Name;

            Cell headCell = new Cell() { DataType = CellValues.String, CellValue = new CellValue("Kategorie")  };
            firstRow.Append(headCell);
            for (int i = 0; i < x.Count(); i++)
            {
                headCell = new Cell() { DataType = CellValues.String, CellValue = new CellValue(x.ElementAt(i).Key) };
                firstRow.Append(headCell);
            }
            
            sheetData.Append(firstRow);
            foreach (Series serie in chart.Daten)
            {
                Row row = new Row();
                Cell Cell = new Cell() { DataType = CellValues.String, CellValue = new CellValue(serie.Name) };
                row.Append(Cell);

                for (int i = 0; i < x.Count(); i++)
                {
                    try
                    {
                        Cell = new Cell() { CellValue = new CellValue(serie.findData(x.ElementAt(i).Key)["Value"]) };
                    }
                    catch (Exception ex)
                    {
                        Cell = new Cell() { CellValue = new CellValue("0") };
                    }
                    row.Append(Cell);
                }
               
                sheetData.Append(row);
            }

            worksheet.Append(sheetData);


            worksheetPart.Worksheet = worksheet;
        }


        private  static  void SetPackageProperties(OpenXmlPackage document)
        {
            document.PackageProperties.Creator = "HDI Charts";
            document.PackageProperties.Created = DateTime.Now;
            document.PackageProperties.Modified = DateTime.Now;
            document.PackageProperties.LastModifiedBy = "HDI Charts";
        }


    }
}
